﻿'**************************************************************************
'Description:问题上报、生产准备类(实现类)
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Zhangll
'create Date:2016.04.23
'Rewriter:2016.04.26
'Rewrite Date:Date:2016.04.27
'**************************************************************************
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports BusinessRule.IBusiness
Imports DatabaseAccess.IDAL
Imports System.Data
Imports BusinessRule.DTO
Imports BusinessRule.Common
Imports DatabaseAccess.DALFactory
Imports External.APICalling
Imports Camstar.WebClient.WebServicesProxy.InSiteWebServices
Imports DatabaseAccess.DBUtility
Imports System.Data.OracleClient

Public Class uMES_JXProblemBusiness
    Implements uMES_JXProblemIBusiness
    Private dal As IOracleDAL = uMESDalContext.instance.GetDalObject()
    ''' <summary>
    ''' 获取用户
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks>Modify YangJiang 20180724 name->para</remarks>
    Public Function GetEmployee(ByVal para As Dictionary(Of String, String)) As DataTable Implements uMES_JXProblemIBusiness.GetEmployee
        'Dim strQuery As String = "SELECT e.employeename,e.fullname,e.employeeid" & vbCrLf & _
        '                           "FROM employee e" & vbCrLf & _
        '                           "LEFT JOIN employeerole er ON er.employeeid = e.employeeid" & vbCrLf & _
        '                           "LEFT JOIN roledef r ON r.roleid = er.roleid" & vbCrLf & _
        '                           "WHERE r.rolename = '" & name & "'" & vbCrLf

        Dim strQuery As String = "SELECT e.employeeid,e.employeename,e.fullname FROM employee e " & vbCrLf & _
                                 "LEFT JOIN sessionvalues se ON se.employeeid=e.employeeid " & vbCrLf & _
                                 "WHERE se.factoryid='" & para("FactoryID") & "' AND SE.WORKCENTERID='" & para("WorkcenterID") & "' " 'Modify YangJiang 20180724
        'Dim strQuery As String = "SELECT e.* FROM employee e LEFT JOIN  sessionvalues s ON s.employeeid=e.employeeid WHERE s.factoryid='" & name & "'"
        GetEmployee = dal.StandardGetDataTable(strQuery)
    End Function
    ''' <summary>
    ''' 获取资源
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetResource() As DataTable Implements uMES_JXProblemIBusiness.GetResource
        Dim strQuery As String = " select rd.resourceename,rd.description from resourcedef rd"
        GetResource = dal.StandardGetDataTable(strQuery)
    End Function
    ''' <summary>
    ''' 获取资源
    ''' </summary>
    ''' <param name="factoryid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetWorkCenter(ByVal factoryid As String) As DataTable Implements uMES_JXProblemIBusiness.GetWorkCenter 'tianfw modify 'ByVal factoryid As String' 2017.08.21
        Dim strQuery As New StringBuilder

        strQuery.AppendLine("SELECT WC.WORKCENTERNAME, WC.WORKCENTERID, U.UFCPROBLEMTYPENAME,U.UFCPROBLEMTYPEID ")
        strQuery.AppendLine("FROM WORKCENTER WC ")
        strQuery.AppendLine("LEFT JOIN UFCPROBLEMTYPE U ON U.UFCPROBLEMTYPEID = WC.UFCPROBLEMTYPEID ")
        strQuery.AppendLine("WHERE WC.UFCPROBLEMTYPEID IS NOT NULL ")
        strQuery.AppendFormat(" AND WC.FACTORYID='{0}'", factoryid)
        GetWorkCenter = dal.StandardGetDataTable(strQuery.ToString)
    End Function
    ''' <summary>
    ''' 获取角色
    ''' </summary>
    ''' <param name="name"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetRole(ByVal name As String) As DataTable Implements uMES_JXProblemIBusiness.GetRole

        Dim strQuery As String = "SELECT r.rolename,r.roleid" & vbCrLf & _
                                           "FROM roledef r" & vbCrLf & _
                                           "LEFT JOIN factory f ON f.factoryid = r.factoryid" & vbCrLf & _
                                           "WHERE f.factoryname = '" & name & "'" & vbCrLf
        GetRole = dal.StandardGetDataTable(strQuery)
    End Function

    ''' <summary>
    ''' 获取车间
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetFactory() As DataTable Implements uMES_JXProblemIBusiness.GetFactory
        Dim strSql As String = "SELECT * FROM factory"
        GetFactory = dal.StandardGetDataTable(strSql)
    End Function

    ''' <summary>
    ''' 保存问题
    ''' </summary>
    ''' <param name="para"></param>
    ''' <param name="strMessage"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveProblemInfo(ByVal para As Dictionary(Of String, String), ByRef strMessage As String) As Boolean Implements uMES_JXProblemIBusiness.SaveProblemInfo
        SaveProblemInfo = False
        Dim dataEntityList As New List(Of ClientAPIEntity)
        Dim dataEntity As New ClientAPIEntity

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataName = "Name"
        dataEntity.ClientDataValue = para("Name")
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntityList.Add(dataEntity)

        If para.ContainsKey("Resource") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "Resource"
            dataEntity.ClientDataValue = para("Resource")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("ufcToEmployee") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcToEmployee"
            dataEntity.ClientDataValue = para("ufcToEmployee")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntityList.Add(dataEntity)
        End If
        If para("strOprType") = "0" Then

            
            If para.ContainsKey("ufcCreateEmployee") Then
                dataEntity = New ClientAPIEntity
                dataEntity.ClientDataName = "ufcCreateEmployee"
                dataEntity.ClientDataValue = para("ufcCreateEmployee")
                dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
                dataEntityList.Add(dataEntity)
            End If
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcCreateDate"
            dataEntity.ClientDataValue = Now.ToString("yyyy-MM-ddTHH:mm:ss")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If
        If para.ContainsKey("WorkCenter") Then
            If para("WorkCenter") <> "其他" Then 'Modify YangJiang 20180724
                dataEntity = New ClientAPIEntity
                dataEntity.ClientDataName = "WorkCenter"
                dataEntity.ClientDataValue = para("WorkCenter")
                dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
                dataEntityList.Add(dataEntity)
            End If
        End If
        If para.ContainsKey("Container") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "Container"
            dataEntity.ClientDataValue = para("Container")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.ContainerField
            dataEntity.ClientOtherValue = "Lot"
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("description") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "description"
            dataEntity.ClientDataValue = para("description")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        'add YangJiang 20180329
        If para.ContainsKey("notes") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "notes"
            dataEntity.ClientDataValue = para("notes")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("ufcContent") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcContent"
            dataEntity.ClientDataValue = para("ufcContent")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If
        If para.ContainsKey("ufcResult") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcResult"
            dataEntity.ClientDataValue = para("ufcResult")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("Factory") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "Factory"
            dataEntity.ClientDataValue = para("Factory")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntityList.Add(dataEntity)
        End If
        ''tianfw add 2017.08.21 添加factoryid
        'If para.ContainsKey("FactoryID") Then
        '    dataEntity = New ClientAPIEntity
        '    dataEntity.ClientDataName = "FactoryID"
        '    dataEntity.ClientDataValue = para("FactoryID")
        '    dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
        '    dataEntityList.Add(dataEntity)
        'End If
        ''
        If para.ContainsKey("ufcChuLiDate") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcChuLiDate"
            dataEntity.ClientDataValue = para("ufcChuLiDate")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("ufcShenHeDate") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcShenHeDate"
            dataEntity.ClientDataValue = para("ufcShenHeDate")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("UfcShenHeEmployee") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcShenHeEmployee"
            dataEntity.ClientDataValue = para("UfcShenHeEmployee")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("WorkflowStepID") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "WorkflowStepID"
            dataEntity.ClientDataValue = para("WorkflowStepID")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("ufcCreateTeam") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcCreateTeam"
            dataEntity.ClientDataValue = para("ufcCreateTeam")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("Product") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "Product"
            dataEntity.ClientDataValue = para("Product")
            dataEntity.ClientDataVersion = para("ProductRevision")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.RevisionedObjectField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("ufcProblemType") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcProblemType"
            dataEntity.ClientDataValue = para("ufcProblemType")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("ufcwtdj") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcWTDJ"
            dataEntity.ClientDataValue = para("ufcwtdj")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("ufcStatus") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcStatus"
            dataEntity.ClientDataValue = para("ufcStatus")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("SuperviseEmployee") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "SuperviseEmployee"
            dataEntity.ClientDataValue = para("SuperviseEmployee")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("SuperviseDate") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "SuperviseDate"
            dataEntity.ClientDataValue = para("SuperviseDate")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("SuperviseOpinion") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "SuperviseOpinion"
            dataEntity.ClientDataValue = para("SuperviseOpinion")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("OperatorEmployee") Then '每次操作人
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "OperatorEmployee"
            dataEntity.ClientDataValue = para("OperatorEmployee")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntityList.Add(dataEntity)
        End If

        Dim api As New CamstarClientAPI(para("userName"), para("passWord"))

        If para("strOprType") = "0" Then
            SaveProblemInfo = api.CreateModelByStandard("ufcProblemMaintDoc", "ufcProblemMaint", "New", dataEntityList, strMessage)
        ElseIf para("strOprType") = "1" Then
            SaveProblemInfo = api.UpdateModelByStandard("ufcProblemMaintDoc", "ufcProblemMaint", para("Name"), "", dataEntityList, strMessage)
        End If
    End Function
    ''' <summary>
    ''' 获取问题列表
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>Modify YangJiang 20180628 已完成->已关闭
    Function GetMfgProblemList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMES_JXProblemIBusiness.GetMfgProblemList
        Dim strQuery As New StringBuilder
        strQuery.AppendLine("SELECT wc.workcentername,wc.workcenterid,up.ufcproblemname,ce.employeename,ce.fullname,")
        strQuery.AppendLine("up.ufccreatedate,f.factoryname,upt.ufcproblemtypename,r.resourcename,c.containername,")
        strQuery.AppendLine("toe.employeename AS toemployeename,toe.fullname AS tofullname,up.ufcwtdj,ws.workflowstepname,ws.workflowstepid,") '增加工序名称和ID查询 add by tianFW 2018年12月18日
        strQuery.AppendLine("up.ufcchulidate,up.ufcshenhedate,pb.productname,p.productrevision,up.description,up.notes,")
        strQuery.AppendLine("up.ufcljxh,up.ufccontent,up.ufcResult,up.superviseopinion,up.supervisedate,se.fullname svfullname,")
        strQuery.AppendLine(" DECODE (up.ufcstatus,'1',ROUND((SYSDATE-up.ufcchulidate)*24 ,2))overtime,up.ufcwtdj priority,")
        strQuery.AppendLine("CASE WHEN up.ufcstatus=0 THEN '已完成' WHEN up.ufcstatus=1 THEN '未处理' WHEN up.ufcstatus=2 THEN '处理中' WHEN up.ufcstatus=3 THEN '处理完' END AS ufcstatus,")
        strQuery.AppendLine("CASE WHEN LOWER(ce.employeename)='' AND up.ufcresult IS NULL THEN 1 ELSE 0 END AS tjrflag,toe.employeeid toemployeeid,up.ufcproblemid,")
        strQuery.AppendLine("HE.employeename shenhename,HE.fullname shenhefullname")
        strQuery.AppendLine("FROM ufcproblem up")
        strQuery.AppendLine("LEFT JOIN workcenter wc ON wc.workcenterid=up.workcenterid")
        strQuery.AppendLine("LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID = up.WORKFLOWSTEPID")
        strQuery.AppendLine("LEFT JOIN employee ce ON ce.employeeid = up.ufccreateemployeeid")
        strQuery.AppendLine("LEFT JOIN resourcedef r ON r.resourceid = up.resourceid")
        strQuery.AppendLine("LEFT JOIN employee toe ON toe.employeeid = up.ufctoemployeeid")
        strQuery.AppendLine("LEFT JOIN product p ON p.productid = up.productid")
        strQuery.AppendLine("LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid")
        strQuery.AppendLine("LEFT JOIN container c ON c.containerid = up.containerid")
        strQuery.AppendLine("LEFT JOIN ufcproblemtype upt ON upt.ufcproblemtypeid = up.ufcproblemtypeid")
        strQuery.AppendLine("LEFT JOIN factory f ON f.factoryid = up.factoryid")
        strQuery.AppendLine("LEFT JOIN employee se ON se.employeeid = up.superviseemployeeid")
        strQuery.AppendLine("LEFT JOIN EMPLOYEE HE ON HE.EMPLOYEEID=UP.ufcshenheemployeeid")
        strQuery.AppendLine("WHERE")
        If para.Keys.Contains("OverTime") Then
            strQuery.AppendLine("(SYSDATE-up.ufcchulidate)>0 ")
        Else
            strQuery.AppendFormat("(up.ufccreateemployeeid='{0}'or  up.ufctoemployeeid = '{0}' or ( up.ufctoemployeeid is null and wc.workcentername='{1}'))",
                                  para("employeeid"), para("WorkCenter"))
        End If
        'zll 20160422 增加：状态查询
        If (para("status").ToString() = "未处理") Then
            strQuery.AppendLine(" and up.ufcstatus='1'")
        ElseIf (para("status").ToString() = "处理中") Then
            strQuery.AppendLine(" and up.ufcstatus='2'")
        ElseIf (para("status").ToString() = "已完成") Then '关闭
            strQuery.AppendLine(" and up.ufcstatus='0'")
        ElseIf (para("status").ToString() = "处理完") Then '处理完成
            strQuery.AppendLine(" and up.ufcstatus='3'")
        End If
        If para.Keys.Contains("ProblemType") = True Then
            If para("ProblemType") <> "全部" Then
                strQuery.AppendFormat(" and upt.ufcproblemtypename='{0}'", para("ProblemType"))
            End If
        End If
        strQuery.AppendFormat(" AND up.FACTORYID='{0}'", para("FactoryID")) 'tianfw add 2017.08.21
        If para.Keys.Contains("CreateEmployee") = True Then
            strQuery.AppendFormat("AND LOWER(ce.fullname) LIKE '%{0}%'", para("CreateEmployee").ToString.ToLower)
        End If
        If para.Keys.Contains("ToEmployee") = True Then
            strQuery.AppendFormat("AND LOWER(toe.fullname) LIKE '%{0}%'", para("ToEmployee").ToString.ToLower)
        End If
        If para.Keys.Contains("ContainerName") = True Then
            strQuery.AppendFormat("AND LOWER(c.containername) = '{0}'", para("ContainerName").ToString.ToLower)
        End If
        If para.Keys.Contains("ProductName") = True Then
            strQuery.AppendFormat("AND (LOWER(pb.productname) LIKE '%{0}%' OR LOWER(p.description) LIKE '%{0}%')", para("ProductName").ToString.ToLower)
        End If
        If para.Keys.Contains("StartDate") = True Then
            strQuery.AppendFormat("AND up.ufccreatedate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss')", para("StartDate").ToString)
        End If
        If para.Keys.Contains("EndDate") = True Then
            strQuery.AppendFormat("AND up.ufccreatedate <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss')", para("EndDate").ToString)
        End If
        strQuery.AppendLine(" ORDER BY up.ufccreatedate DESC") 'add YangJiang 20180723 问题上报按提出时间排序

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strQuery.ToString
        Dim result As New uMESProcessResult

        Try
            Dim r As uMESProcessResult = dal.RunProcedureForPage(paraPage)

            result.Result = True
            result.DBTable = r.DBTable
            result.RowCount = r.RowCount

        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function

    ''' <summary>
    ''' 获取问题上报单位
    ''' add:Wangjh 20181119
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetUfcProbleType() As DataTable Implements uMES_JXProblemIBusiness.GetUfcProbleType
        Dim strSql As String = "SELECT *  FROM ufcproblemtype upt "
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function
    ''' <summary>
    ''' 获取问题上报类型
    ''' add:Wangjh 20181119
    ''' </summary>
    ''' <param name="name"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetUfcProbleTypeList(ByVal name As String) As DataTable Implements uMES_JXProblemIBusiness.GetUfcProbleTypeList
        Dim strSql As String = <string>
                                SELECT upl.*  FROM ufcproblemtypeproblemslist  upl
                                LEFT JOIN ufcproblemtype upt ON upt.ufcproblemtypeid=upl.ufcproblemtypeid
                               </string>
        strSql += String.Format("WHERE upt.ufcproblemtypename='{0}'", name)
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function


    ''' <summary>
    ''' 获取批次SQL
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetContainerInfoSQL() As String
        Dim strQuery As String = "SELECT c.containername,pb.productname,c.originalqty,p.description,sb.specname," & vbCrLf & _
                                 "p.productrevision,c.qty,c.plannedstartdate,c.plannedcompletiondate,wfs.workflowstepname " & vbCrLf & _
                                "FROM container c" & vbCrLf & _
                                "LEFT JOIN currentstatus cs ON cs.currentstatusid = c.currentstatusid" & vbCrLf & _
                                "LEFT JOIN product p ON p.productid = c.productid" & vbCrLf & _
                                "LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid" & vbCrLf & _
                                "LEFT JOIN productfamily pf ON pf.productfamilyid = p.productfamilyid" & vbCrLf & _
                                "LEFT JOIN producttype pt ON pt.producttypeid = p.producttypeid" & vbCrLf & _
                                "LEFT JOIN spec s ON s.specid = cs.specid" & vbCrLf & _
                                "LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "LEFT JOIN factory f ON f.factoryid = cs.factoryid" & vbCrLf & _
                                "LEFT JOIN workflowstep wfs ON wfs.workflowstepid = cs.workflowstepid" & vbCrLf & _
                                "LEFT JOIN workflow wf ON wf.workflowid = wfs.workflowid" & vbCrLf & _
                                "LEFT JOIN workflowbase wfb ON wfb.workflowbaseid = wf.workflowbaseid" & vbCrLf & _
                                "WHERE c.status<>2 and c.parentcontainerid is null and c.splitfromid is null" & vbCrLf
        Return strQuery
    End Function
    ''' <summary>
    ''' 获取批次信息
    ''' </summary>
    ''' <param name="strContainerName"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetContainerInfo(ByVal strContainerName As String) As uMESProcessResult Implements uMES_JXProblemIBusiness.GetContainerInfo
        Dim strQuery As String = GetContainerInfoSQL()
        strQuery += "AND c.containername = '" & strContainerName & "'" & vbCrLf

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetContainerInfo(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function

    Public Function SavePrepareInfo(ByVal para As Dictionary(Of String, String), ByRef strMessage As String) As Boolean Implements uMES_JXProblemIBusiness.SavePrepareInfo

        Dim dataEntityList As New List(Of ClientAPIEntity)
        Dim dataEntity As New ClientAPIEntity

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataName = "Name"
        dataEntity.ClientDataValue = para("Name")
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataName = "Notes"
        dataEntity.ClientDataValue = para("Title")
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntityList.Add(dataEntity)

        '准备类型
        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataName = "ufcLJXH"
        dataEntity.ClientDataValue = para("type")
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntityList.Add(dataEntity)

        '准备工序
        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataName = "Description"
        dataEntity.ClientDataValue = para("spec")
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntityList.Add(dataEntity)

        If para.ContainsKey("WorkCenter") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "WorkCenter"
            dataEntity.ClientDataValue = para("WorkCenter")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntityList.Add(dataEntity)
        End If

        If para("toEmployee").ToString().Trim() <> "" Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcToEmployee"
            dataEntity.ClientDataValue = para("toEmployee")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("ufcChuLiDate") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcChuLiDate"
            dataEntity.ClientDataValue = para("ufcChuLiDate")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        If para("Resource").ToString().Trim() <> "" Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "Resource"
            dataEntity.ClientDataValue = para("Resource")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("ufcContent") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcContent"
            dataEntity.ClientDataValue = para("ufcContent")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("Container") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "Container"
            dataEntity.ClientDataValue = para("Container")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.ContainerField
            dataEntity.ClientOtherValue = "Lot"
            dataEntityList.Add(dataEntity)
        End If

        If para("strOprType") = "0" Then
            If para.ContainsKey("ufcCreateEmployee") Then
                dataEntity = New ClientAPIEntity
                dataEntity.ClientDataName = "ufcCreateEmployee"
                dataEntity.ClientDataValue = para("ufcCreateEmployee")
                dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
                dataEntityList.Add(dataEntity)
            End If
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcCreateDate"
            dataEntity.ClientDataValue = Now.ToString("yyyy-MM-ddTHH:mm:ss")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("ufcProblemType") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcProblemType"
            dataEntity.ClientDataValue = para("ufcProblemType")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("ufcStatus") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcStatus"
            dataEntity.ClientDataValue = para("ufcStatus")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        Dim api As New CamstarClientAPI(para("userName"), para("passWord"))

        Dim isOK As Boolean = False

        If para("strOprType") = "0" Then
            isOK = api.CreateModelByStandard("ufcProblemMaintDoc", "ufcProblemMaint", "New", dataEntityList, strMessage)
        ElseIf para("strOprType") = "1" Then
            isOK = api.UpdateModelByStandard("ufcProblemMaintDoc", "ufcProblemMaint", para("Name"), "", dataEntityList, strMessage)
        End If

        Return isOK

    End Function

    Public Function GetPrepareList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMES_JXProblemIBusiness.GetPrepareList

        Dim strQuery As String = String.Format("SELECT up.ufcproblemid,up.ufcproblemname,up.notes AS title,up.description AS specid,up.containerid,up.ufccontent,up.ufcresult,up.workcenterid," & vbCrLf & _
                                 "up.ufcljxh AS repType,up.ufctoemployeeid,up.ufcchulidate," & vbCrLf & _
                                 "up.resourceid,up.ufccreatedate,up.ufccreateteamid ,upt.ufcproblemtypeid,sb.specname,c.containername,pb.productname,p.description AS prodesc," & vbCrLf & _
                                 "wc.workcentername,toe.fullname AS tofullname," & vbCrLf & _
                                 "toe.employeename AS toemployeename,upt.ufcproblemtypename,ce.employeename AS cemployeename,ce.fullname AS cfullname, " & vbCrLf & _
                                 "CASE WHEN up.ufcstatus=0 THEN '已处理' WHEN up.ufcstatus=1 THEN '未处理' WHEN up.ufcstatus=2 THEN '处理中' END AS ufcstatus,r.resourcename FROM ufcproblem up " & vbCrLf & _
                                 "LEFT JOIN spec s ON s.specid=up.description " & vbCrLf & _
                                 "LEFT JOIN specbase sb ON sb.specbaseid=s.specbaseid " & vbCrLf & _
                                 "LEFT JOIN CONTAINER c ON c.containerid=up.containerid " & vbCrLf & _
                                 "LEFT JOIN product p ON p.productid=c.productid " & vbCrLf & _
                                 "LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid " & vbCrLf & _
                                 "LEFT JOIN Workcenter wc ON wc.workcenterid=up.workcenterid " & vbCrLf & _
                                 "LEFT JOIN employee toe ON toe.employeeid = up.ufctoemployeeid " & vbCrLf & _
                                 "LEFT JOIN employee ce ON ce.employeeid = up.ufccreateemployeeid " & vbCrLf & _
                                 "LEFT JOIN ufcproblemtype upt ON upt.ufcproblemtypeid = up.ufcproblemtypeid " & vbCrLf & _
                                 "LEFT JOIN resourcedef r ON r.resourceid=up.resourceid " & vbCrLf & _
                                 "WHERE upt.ufcproblemtypename='生产准备' ")

        If para.Keys.Contains("ToWorkCenter") = True Then
            strQuery += "AND wc.workcentername LIKE '%" & para("ToWorkCenter").ToString & "%'" & vbCrLf
        End If

        If para.Keys.Contains("CreateEmployee") = True Then
            strQuery += "AND LOWER(ce.fullname) LIKE '%" & para("CreateEmployee").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.Keys.Contains("ToEmployee") = True Then
            strQuery += "AND LOWER(toe.fullname) LIKE '%" & para("ToEmployee").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.Keys.Contains("ContainerName") = True Then
            strQuery += "AND LOWER(c.containername) = '" & para("ContainerName").ToString.ToLower & "'" & vbCrLf
        End If
        If para.Keys.Contains("specno") = True Then
            strQuery += "AND s.specno = '" & para("specno").ToString & "'" & vbCrLf
        End If
        If para.Keys.Contains("ProductName") = True Then
            strQuery += "AND (LOWER(pb.productname) LIKE '%" & para("ProductName").ToString.ToLower & "%' OR LOWER(p.description) LIKE '%" & para("ProductName").ToString.ToLower & "%')" & vbCrLf
        End If
        If para.Keys.Contains("StartDate") = True Then
            strQuery += "AND up.ufccreatedate >= TO_DATE('" & para("StartDate").ToString & "','yyyy-MM-dd')" & vbCrLf
        End If
        If para.Keys.Contains("EndDate") = True Then
            strQuery += "AND up.ufccreatedate <= TO_DATE('" & para("EndDate").ToString & "','yyyy-MM-dd')" & vbCrLf
        End If
        'zll 20160422 增加：状态查询
        If (para("status").ToString() = "未处理") Then
            strQuery += " and up.ufcstatus=1"
            'ElseIf (para("status").ToString() = "处理中") Then
            '    strQuery += " and up.ufcstatus='2'"
        ElseIf (para("status").ToString() = "已处理") Then
            strQuery += " and up.ufcstatus=0"
        End If

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strQuery
        Dim result As New uMESProcessResult

        Try
            Dim r As uMESProcessResult = dal.RunProcedureForPage(paraPage)
            Dim CommonObject As uMESCommonBusiness = New uMESCommonBusiness()

            For Each row In r.DBTable.Rows
                row("SpecName") = CommonObject.GetSpecNameWithoutProduct(row("SpecName").ToString)
            Next

            result.Result = True
            result.DBTable = r.DBTable
            result.RowCount = r.RowCount

        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function

    Public Function GetToolInfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMES_JXProblemIBusiness.GetToolInfo
        Dim strQuery As String = String.Format("SELECT t.toolinfoname,t.description,t.productid,t.specid,t.tooltypeid ,t.qty, pb.productname,sb.specname,ty.tooltypename  FROM toolinfo t " & vbCrLf & _
                                                "LEFT JOIN product p ON p.productid=t.productid " & vbCrLf & _
                                                "LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid " & vbCrLf & _
                                                "LEFT JOIN spec s ON s.specid=t.specid " & vbCrLf & _
                                                "LEFT JOIN specbase sb ON sb.specbaseid=s.specbaseid " & vbCrLf & _
                                                "LEFT JOIN tooltype ty ON ty.tooltypeid=t.tooltypeid " & vbCrLf & _
                                                "WHERE pb.productname='{0}'  AND sb.specname='{1}'", para("ProductName").ToString(), para("specname").ToString())
        Dim result As New uMESProcessResult
        Dim DT As DataTable = dal.GetContainerInfo(strQuery)
        result.Result = True
        result.DBTable = DT.Copy()
        result.RowCount = DT.Rows.Count
        Return result
    End Function

    Public Function FinishPrepareInfo(ByVal para As Dictionary(Of String, String), ByRef strMessage As String) As Boolean Implements uMES_JXProblemIBusiness.FinishPrepareInfo

        Dim dataEntityList As New List(Of ClientAPIEntity)
        Dim dataEntity As New ClientAPIEntity

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataName = "Name"
        dataEntity.ClientDataValue = para("Name")
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntityList.Add(dataEntity)

        If para.ContainsKey("ufcShenHeDate") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcShenHeDate"
            dataEntity.ClientDataValue = para("ufcShenHeDate")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("ufcResult") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcResult"
            dataEntity.ClientDataValue = para("ufcResult")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("ufcShenHeEmployee") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcShenHeEmployee"
            dataEntity.ClientDataValue = para("ufcShenHeEmployee")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("ufcStatus") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcStatus"
            dataEntity.ClientDataValue = para("ufcStatus")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        Dim api As New CamstarClientAPI(para("userName"), para("passWord"))

        Dim isOK As Boolean = False

        isOK = api.UpdateModelByStandard("ufcProblemMaintDoc", "ufcProblemMaint", para("Name"), "", dataEntityList, strMessage)

        Return isOK

    End Function

    ''' <summary>
    ''' 获取人员信息
    ''' add:Wangjh 20180118
    ''' </summary>
    ''' <param name="employeeName"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetEmployeeInfoByName(ByVal employeeName As String) As DataTable Implements uMES_JXProblemIBusiness.GetEmployeeInfoByName
        Dim strSql As String = String.Format("SELECT * FROM employee e WHERE e.employeename='{0}'", employeeName)
        GetEmployeeInfoByName = DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 插入转发信息
    ''' add:Wangjh 20180119
    ''' </summary>
    ''' <param name="Para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function InsertRelayProblem(ByVal Para As Dictionary(Of String, String)) As Boolean Implements uMES_JXProblemIBusiness.InsertRelayProblem
        Dim insertSql As String = "INSERT INTO RelayProblem rp (rp.createdate,rp.employeeid,rp.oldtoemployeeid,rp.newtoemployeeid,rp.relayreasonnote,rp.ufcproblemid) VALUES (sysdate,:employeeid,:oldtoemployeeid,:newtoemployeeid,:relayreasonnote,:ufcproblemid)"

        Dim insertPara(4) As OracleParameter

        insertPara(0) = DatabaseHelper.NewParameter("employeeid", Para("EmployeeID"), OracleType.VarChar)
        insertPara(1) = DatabaseHelper.NewParameter("oldtoemployeeid", Para("OldToEmployeeID"), OracleType.VarChar)
        insertPara(2) = DatabaseHelper.NewParameter("newtoemployeeid", Para("NewToEmployeeID"), OracleType.VarChar)
        insertPara(3) = DatabaseHelper.NewParameter("relayreasonnote", Para("RelayReasonNote"), OracleType.VarChar)
        insertPara(4) = DatabaseHelper.NewParameter(":ufcproblemid", Para("UfcProblemID"), OracleType.VarChar)

        Dim i As Integer = DatabaseHelper.ExecuteSql(insertSql, insertPara)
        If i > 0 Then
            InsertRelayProblem = True
        Else
            InsertRelayProblem = False
        End If
    End Function

    ''' <summary>
    ''' 获取转发信息
    ''' add：Wangjh 20180119
    ''' </summary>
    ''' <param name="Para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetRelayProblemInfo(ByVal Para As Dictionary(Of String, String)) As DataTable Implements uMES_JXProblemIBusiness.GetRelayProblemInfo
        Dim strSql As String = "SELECT * FROM RelayProblem rp"
        strSql += String.Format(" where rp.Ufcproblemid='{0}'", Para("UfcProblemID"))
        strSql += " ORDER BY rp.createdate DESC"
        GetRelayProblemInfo = DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 获取审核日志
    ''' </summary>
    ''' <param name="problemID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetProblemResultRecord(ByVal problemID As String) As OracleDataReader Implements uMES_JXProblemIBusiness.GetProblemResultRecord
        Dim strSql As String = "SELECT up.ufcstatus,m.objectinstanceid,m.objectname,m.executeaction,m.systemdate,m.txndate,mt.audittrailinfo FROM modelingaudittrailheader m" &
                            " INNER JOIN Modelingaudittrail mt ON mt.modelingaudittrailheaderid=m.modelingaudittrailheaderid" &
                            " LEFT JOIN ufcproblem up ON up.ufcproblemid=m.objectinstanceid"

        strSql += String.Format(" WHERE m.objectinstanceid='{0}'", problemID)
        strSql += " ORDER BY m.txndate ASC"
        GetProblemResultRecord = DatabaseHelper.ExecuteReader(strSql)
    End Function

    ''' <summary>
    ''' 获取问题反馈 add:YangJiang 20180416
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetProblemInfo(ByVal para As System.Collections.Generic.Dictionary(Of String, String)) As DTO.uMESProcessResult Implements IBusiness.uMES_JXProblemIBusiness.GetProblemInfo

        Dim strSQL As New StringBuilder
        strSQL.AppendLine(" SELECT  up.description ufcProblemName,wc.workcentername,toe.fullname AS toemployeename,ce.fullname employeename,up.ufccreatedate,")
        strSQL.AppendLine(" CASE WHEN ROUND((SYSDATE-up.ufcchulidate)*24 ,2)>0 THEN ROUND((SYSDATE-up.ufcchulidate)*24 ,2)  WHEN ROUND((SYSDATE-up.ufcchulidate)*24 ,2)<=0 THEN 0 END AS delayday,")
        strSQL.AppendLine(" CASE  WHEN up.ufcstatus=1 THEN '未处理' WHEN up.ufcstatus=2 THEN '处理中' WHEN up.ufcstatus=3 THEN '处理完' END AS ufcstatus")
        strSQL.AppendLine(" FROM ufcproblem up")
        strSQL.AppendLine(" LEFT JOIN ufcproblemtype upt ON upt.ufcproblemtypeid = up.ufcproblemtypeid")
        strSQL.AppendLine(" LEFT JOIN employee ce ON ce.employeeid = up.ufccreateemployeeid")
        strSQL.AppendLine(" LEFT JOIN resourcedef r ON r.resourceid = up.resourceid")
        strSQL.AppendLine(" LEFT JOIN sessionvalues sv ON sv.employeeid = ce.employeeid")
        strSQL.AppendLine(" LEFT JOIN employee toe ON toe.employeeid = up.ufctoemployeeid")
        strSQL.AppendLine(" LEFT JOIN workcenter wc ON wc.workcenterid = up.workcenterid")
        strSQL.AppendLine(" LEFT JOIN factory f ON f.factoryid = up.factoryid")
        strSQL.AppendLine(" LEFT JOIN product p ON p.productid = up.productid")
        strSQL.AppendLine(" LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid")
        strSQL.AppendLine(" LEFT JOIN container c ON c.containerid = up.containerid WHERE up.ufcstatus <> 0")
        strSQL.AppendFormat(" AND ((up.ufctoemployeeid IS NULL AND up.workcenterid='{0}')", para("WorkCenterID"))
        strSQL.AppendFormat(" OR (up.ufctoemployeeid='{0}' OR up.ufccreateemployeeid='{1}'))", para("EmployeeID"), para("EmployeeID"))
        Dim result As New uMESProcessResult
        Try
            Dim DT As DataTable = dal.GetMainViewInfo(strSQL.ToString)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try
        Return result
    End Function

    ''' <summary>
    ''' 获取零件进度表的批次工序问题上报信息 create by tianFW 2018年12月19日
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetProblemInfo_ProgressOfProduct(ByVal para As Dictionary(Of String, String)) As DataTable Implements uMES_JXProblemIBusiness.GetProblemInfo_ProgressOfProduct
        Dim strsql As New StringBuilder
        Dim strs_uvale As String =
            <string>
                SELECT wc.workcentername,up.ufccontent,cem.fullname Createfullname,tem.fullname DutyOfficer,
                up.ufcshenhedate,sem.fullname shenhefullname,upt.ufcproblemtypename,up.description,up.ufccreatedate,
                up.ufcchulidate,DECODE(up.ufcWTDJ,0,'特急（1H)',1,'紧急（4H)',2,'紧急（4H)',3,'一般（3天)') ufcWTDJvalue,up.ufcwtdj,
                DECODE(up.ufcstatus,0,'已完成'，1,'未处理',2,'处理中',3,'处理完') ufcstatus,
                DECODE (up.ufcstatus,1,ROUND((SYSDATE-up.ufcchulidate)*24 ,2)) overtime
                FROM ufcproblem up 
                LEFT JOIN Workcenter wc ON wc.workcenterid = up.workcenterid
                LEFT JOIN employee cem ON cem.employeeid = up.ufccreateemployeeid
                LEFT JOIN employee tem ON tem.employeeid = up.ufctoemployeeid
                LEFT JOIN EMPLOYEE sem ON sem.EMPLOYEEID= UP.ufcshenheemployeeid
                LEFT JOIN ufcproblemtype upt ON upt.ufcproblemtypeid = up.ufcproblemtypeid
            </string>
        strsql.AppendLine(strs_uvale)
        strsql.AppendFormat(" WHERE UP.CONTAINERID ='{0}' AND UP.WORKFLOWSTEPID='{1}'", para("ContainerID"), para("WorkFlowStepID"))
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function
End Class
